package cn.edu.upc.letao.dao;

import cn.edu.upc.letao.entity.CommonMember;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.awt.print.Pageable;
import java.util.List;

/**
 * Created by cheney on 2017/4/5.
 */
public interface CommonMemberDao extends CrudRepository<CommonMember,Integer> {
    List<CommonMember> findByEmailAndPassword(String email, String password);

    List<CommonMember> findByEmail(String email);

    CommonMember findByUid(int uid);

    CommonMember findOneByUid(int uid);//用于用户的个人信息显示，炜荣

    //根据金币 获取top5的用户
    @Query(value = "SELECT * FROM lt_common_member ORDER BY gold DESC LIMIT 5", nativeQuery = true)
    public List<CommonMember> getTop5Memebers();

    //获得未审核的用户
    @Query(value = "SELECT * FROM lt_common_member WHERE status <> 0 AND status <> 1", nativeQuery = true)
    List<CommonMember> findUncheck();

    //计算未审核总用户数量
    @Query(value = "SELECT COUNT(*) FROM lt_common_member WHERE status <> 0 AND status <> 1", nativeQuery = true)
    Long countUncheck();

    //所有管理员
    @Query(value = "SELECT * FROM lt_common_member WHERE adminid = 1 AND status <> 1", nativeQuery = true)
    List<CommonMember> findAllAdmin();

    //删除用户
    @Modifying
    @Query(value = "UPDATE lt_common_member member SET status = 1 WHERE uid = ?1", nativeQuery = true)
    int delete(int uid);

    //查找并筛选
    @Query(value = "SELECT * FROM lt_common_member WHERE uid LIKE %?1% OR username LIKE %?1% OR email LIKE %?1%  AND status <> 1 limit ?2,20", nativeQuery = true)
    List<CommonMember> findAndFilter(String name,int page);

    //查找并分页
    @Query(value = "SELECT * FROM lt_common_member  WHERE status <> 1 limit ?1,20" , nativeQuery = true)
    List<CommonMember> findPageable(int page);

    //计算总用户数量
    @Query(value = "SELECT COUNT(*) FROM lt_common_member WHERE status <> 1", nativeQuery = true)
    Long countAll();

    //根据email或username或uid找管理员
    @Query(value = "SELECT * FROM lt_common_member WHERE (username = ?1 OR email = ?1 OR uid = ?1) AND status <> 1 AND adminid = 1 limit 1", nativeQuery = true)
    List<CommonMember> findOneAdmin(String credit);
}